const Database = require('better-sqlite3');
const path = require('path');

// Connect to the database
const dbPath = path.join(__dirname, 'data', 'delivery-management.db');
const db = new Database(dbPath, { readonly: true });

console.log('=== DEBUGGING FIELD CONVERSION ISSUE ===\n');

// 1. Check the field metadata for table_c2 (naming table)
console.log('1. FIELD METADATA FOR table_c2 (naming):');
const c2Fields = db.prepare(`
  SELECT id, field_name, field_column 
  FROM parameter_field_metadata 
  WHERE table_name = 'table_c2' 
  ORDER BY field_sequence
`).all();

console.log(`Found ${c2Fields.length} fields:`);
c2Fields.forEach(field => {
  const embeddedFieldName = `naming_${field.field_name}`;
  console.log(`  Field ID: ${field.id}`);
  console.log(`    field_name: "${field.field_name}"`);
  console.log(`    field_column: "${field.field_column}"`);
  console.log(`    Expected embedded field: "${embeddedFieldName}"`);
  console.log('');
});

// 2. Check actual device instance table structure
console.log('\n2. ACTUAL EMBEDDED FIELDS IN device_instances_piping_centrifugal_pump:');
const schema = db.prepare(`PRAGMA table_info(device_instances_piping_centrifugal_pump)`).all();
const namingFields = schema.filter(col => col.name.startsWith('naming_'));

console.log(`Found ${namingFields.length} naming fields:`);
namingFields.forEach(field => {
  console.log(`  - ${field.name}`);
});

// 3. Compare field metadata names with embedded field names
console.log('\n3. FIELD NAME COMPARISON:');
console.log('Checking if field metadata names match embedded field suffixes...\n');

c2Fields.forEach(field => {
  const expectedEmbeddedField = `naming_${field.field_name}`;
  const actualMatch = namingFields.find(f => f.name === expectedEmbeddedField);
  
  console.log(`Field ID ${field.id}:`);
  console.log(`  Metadata field_name: "${field.field_name}"`);
  console.log(`  Expected embedded: "${expectedEmbeddedField}"`);
  console.log(`  Actual match found: ${actualMatch ? 'YES' : 'NO'}`);
  if (actualMatch) {
    console.log(`  Matched field: "${actualMatch.name}"`);
  } else {
    // Try to find a similar field
    const similarFields = namingFields.filter(f => 
      f.name.includes(field.field_name) || 
      field.field_name.includes(f.name.replace('naming_', ''))
    );
    if (similarFields.length > 0) {
      console.log(`  Similar fields found:`);
      similarFields.forEach(f => console.log(`    - ${f.name}`));
    }
  }
  console.log('');
});

// 4. Check if the issue is in the field ID to field name conversion
console.log('\n4. SIMULATING THE CONVERSION PROCESS:');
console.log('If we get field ID data like {611: "TEST_CODE", 612: "PURCHASE_CODE"}...\n');

const sampleFieldIdData = {
  '611': 'TEST_DESIGN_CODE',
  '612': 'TEST_PURCHASE_CODE'
};

console.log('Sample input field ID data:', sampleFieldIdData);
console.log('\nConversion process:');

Object.entries(sampleFieldIdData).forEach(([fieldId, value]) => {
  const field = c2Fields.find(f => f.id === parseInt(fieldId));
  if (field) {
    const embeddedFieldName = `naming_${field.field_name}`;
    console.log(`  Field ID ${fieldId} -> field_name "${field.field_name}" -> embedded "${embeddedFieldName}" = "${value}"`);
    
    // Check if this embedded field exists in the actual table
    const actualField = namingFields.find(f => f.name === embeddedFieldName);
    console.log(`    Target field exists: ${actualField ? 'YES' : 'NO'}`);
  } else {
    console.log(`  Field ID ${fieldId} NOT FOUND in metadata`);
  }
});

db.close();
console.log('\n=== DEBUG COMPLETE ===');